package capaDatos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import utiles.UIDGenerator;

import capaLogica.Multimedia;
import capaLogica.Noticia;

public class MultimediaDAO {

	/**
	 * Atributo que crea la conexion a la bd
	 */
	private Connection conn;
	
	/**
	 * Constructor de la clase
	 */
	public MultimediaDAO(){
		conn = (Connection) ConnectionManager.getInstance().checkOut();
	}
	
	@Override
	 protected void finalize() {
       ConnectionManager.getInstance().checkIn(conn);
	}
	
	 public List<Multimedia> recuperarFotosByEvento(String idEvento) {
	        PreparedStatement stmt = null;
	        ResultSet result = null;
	        List<Multimedia> listaM = new ArrayList<Multimedia>();
	        String sql = "SELECT * FROM multimediaEvento WHERE (idEvento = ?) ";

	        try {
	            stmt = conn.prepareStatement(sql);
	            stmt.setString(1, idEvento);
	            result = stmt.executeQuery();

	            while( result.next() ){
		            String idMulti = result.getString("idMultimedia");
		            Multimedia m = recuperarMultimediaById(idMulti);
		            if ( "0".equals(m.getEsVideo())){
		            	listaM.add(m);
		            }
	            }
	            
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (result != null) {
	                    result.close();
	                }
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
	        return listaM;
	    }
	 
	 public Multimedia recuperarMultimediaById(String idFoto) {
	        PreparedStatement stmt = null;
	        ResultSet result = null;
	        Multimedia m = null ;
	        String sql = "SELECT * FROM multimedia WHERE (idMultimedia = ?)";

	        try {
	            stmt = conn.prepareStatement(sql);
	            stmt.setString(1, idFoto);
	            result = stmt.executeQuery();

	            if ( result.next() ){
	            	m = new Multimedia();
	            	m.setDescripcion(result.getString("descripcion"));
	            	m.setRuta(result.getString("ruta"));
	            	m.setIdMultimedia(result.getString("idMultimedia"));
	            	m.setEsVideo(result.getString("esVideo"));
	            }
	            
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (result != null) {
	                    result.close();
	                }
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
	        return m;
	    }
	 
	 public List<Multimedia> recuperarVideoByEvento(String idEvento) {
	        PreparedStatement stmt = null;
	        ResultSet result = null;
	        List<Multimedia> listaM = new ArrayList<Multimedia>();
	        String sql = "SELECT * FROM multimediaEvento WHERE (idEvento = ?) ";

	        try {
	            stmt = conn.prepareStatement(sql);
	            stmt.setString(1, idEvento);
	            result = stmt.executeQuery();

	            while( result.next() ){
		            String idMulti = result.getString("idMultimedia");
		            Multimedia m = recuperarMultimediaById(idMulti);
		            String esVideo = m.getEsVideo();
		            if ( "1".equals(esVideo) ){
		            	listaM.add(m);
		            }
	            }
	            
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (result != null) {
	                    result.close();
	                }
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
	        return listaM;
	    }
	 
	 public Multimedia insertarMultimedia(Multimedia n) {
			String sql = "INSERT INTO multimedia (idMultimedia,descripcion,ruta,esVideo) VALUES (?,?,?,?) ";
			PreparedStatement stmt = null;
			
			try {
				stmt = conn.prepareStatement(sql);
				n.setIdMultimedia(UIDGenerator.getInstance().getKey());
	            
				stmt.setString(1, n.getIdMultimedia());
				stmt.setString(2, n.getDescripcion());
				stmt.setString(3,	n.getRuta());
				stmt.setString(4, n.getEsVideo());
			
				stmt.executeUpdate();

			} catch (SQLException e) {
				System.out.println("Message: " + e.getMessage());
				System.out.println("SQLState: " + e.getSQLState());
				System.out.println("ErrorCode: " + e.getErrorCode());
			} finally {
				try {
					if (stmt != null) {
						stmt.close();
						
					}
				} catch (SQLException e) {
				}
			}
			
			return n;
		}
	 
	 public void insertarAsociativaEvento(String idEvento, String idMultimedia) {
			String sql = "INSERT INTO multimediaEvento (idMultimediaEvento,idEvento,idMultimedia) VALUES (?,?,?) ";
			PreparedStatement stmt = null;
			
			try {
				stmt = conn.prepareStatement(sql);
	            
				stmt.setString(1, UIDGenerator.getInstance().getKey());
				stmt.setString(2, idEvento);
				stmt.setString(3, idMultimedia);
			
				stmt.executeUpdate();

			} catch (SQLException e) {
				System.out.println("Message: " + e.getMessage());
				System.out.println("SQLState: " + e.getSQLState());
				System.out.println("ErrorCode: " + e.getErrorCode());
			} finally {
				try {
					if (stmt != null) {
						stmt.close();
						
					}
				} catch (SQLException e) {
				}
			}
			
		}
	 
	 public void insertarAsociativaPerfil(String idPerfil, String idMultimedia) {
			String sql = "INSERT INTO multimediaPerfil (idMultimediaPerfil,idPerfil,idMultimedia) VALUES (?,?,?) ";
			PreparedStatement stmt = null;
			
			try {
				stmt = conn.prepareStatement(sql);
	            
				stmt.setString(1, UIDGenerator.getInstance().getKey());
				stmt.setString(2, idPerfil);
				stmt.setString(3, idMultimedia);
			
				stmt.executeUpdate();

			} catch (SQLException e) {
				System.out.println("Message: " + e.getMessage());
				System.out.println("SQLState: " + e.getSQLState());
				System.out.println("ErrorCode: " + e.getErrorCode());
			} finally {
				try {
					if (stmt != null) {
						stmt.close();
						
					}
				} catch (SQLException e) {
				}
			}
			
		}
	 
	 public void borrarMultimedia(String idMultimedia) {
			PreparedStatement stm = null;
			String sql = "DELETE  FROM multimedia WHERE (idMultimedia = ?)";
			try {
				stm = conn.prepareStatement(sql);
				stm.setString(1, idMultimedia);
				stm.executeUpdate();
				
				borrarAsociativaEvento(idMultimedia);
				borrarAsociativaPerfil(idMultimedia);
				
			} catch (SQLException e) {
				System.out.println("SQLMessage: " + e.getMessage());
				System.out.println("SQLState: " + e.getSQLState());
				System.out.println("ErrorCode: " + e.getErrorCode());
			} finally {
				try {
					if (stm != null) {
						stm.close();
					}
				} catch (SQLException e) {

				}
			}
		}	
	 
	 public void borrarAsociativaEvento(String idMultimedia) {
			PreparedStatement stm = null;
			String sql = "DELETE  FROM multimediaEvento WHERE (idMultimedia = ? )";
			try {
				stm = conn.prepareStatement(sql);
				stm.setString(1, idMultimedia);
				stm.executeUpdate();
				
			} catch (SQLException e) {
				System.out.println("SQLMessage: " + e.getMessage());
				System.out.println("SQLState: " + e.getSQLState());
				System.out.println("ErrorCode: " + e.getErrorCode());
			} finally {
				try {
					if (stm != null) {
						stm.close();
					}
				} catch (SQLException e) {

				}
			}
		}	
	 
	 public void borrarAsociativaPerfil(String idMultimedia) {
			PreparedStatement stm = null;
			String sql = "DELETE  FROM multimediaPerfil WHERE (idMultimedia = ? )";
			try {
				stm = conn.prepareStatement(sql);
				stm.setString(1, idMultimedia);
				stm.executeUpdate();
				
			} catch (SQLException e) {
				System.out.println("SQLMessage: " + e.getMessage());
				System.out.println("SQLState: " + e.getSQLState());
				System.out.println("ErrorCode: " + e.getErrorCode());
			} finally {
				try {
					if (stm != null) {
						stm.close();
					}
				} catch (SQLException e) {

				}
			}
		}	
}
